import re
import os
import pdb
import sys
import types
import urllib.request
import time
import datetime
import configparser
import xml.etree.ElementTree as xmltree
'''
This is unused
try:
    import kinterbasdb; kinterbasdb.init(type_conv=200)
except Exception as err:
    print('WARNING: can\'t load "kinterbasdb": %s' % (str(err)))
'''

try:
    import pyodbc
    MSDBDriver = "pyodbc"
except:
    try:
        import pymssql
        MSDBDriver = "pymssql"
    except Exception as err:
        print('WARNING: can\'t load noone of "pyodbc" and "pymssql": %s' % str(err))


class SimpleLogger:
    FATAL = 1
    ERROR = 2
    WARNING = 3
    INFO = 4
    DEBUG = 5

    levels = \
    {
     FATAL     : 'FATAL',
     ERROR     : 'ERROR',
     WARNING   : 'WARNING',
     INFO      : 'INFO',
     DEBUG     : 'DEBUG'
    }
    DATEFORMAT = '%Y-%m-%dT%H:%M:%S'

    def __init__(self):
        self.flog   = None
        self.fpath  = None
        self.level  = SimpleLogger.INFO
        self.ident  = ''

    @staticmethod
    def formatmsg(level, msg):
            #time stamp
        stamp = datetime.datetime.now().strftime(SimpleLogger.DATEFORMAT)
        return '%s %s %s' % (stamp, str(level), str(msg))

    def setfile(self, path):
        try:
            self.flog = open(path, 'w')
            self.fpath = path
        except Exception as err:
            msg = formatmsg(SimpleLogger.FATAL, 'setfile: ' + str(err))
            print(msg)

    def setlevel(self, level):
        if level in SimpleLogger.levels.keys():
            self.level = level
        else:
            raise RuntimeError('SimpleLogger::formatmsg: unknown level(%s)' % str(level))

    def setident(self, ident):
        if not isinstance(ident, str): raise RuntimeError('SimpleLogger::setident: ident should be a string')
        self.ident = ident

    def log(self, level, msg):
        if self.level >= level:
            fullmsg = self.formatmsg(self.levels[level], self.ident + ' ' + msg)
            print(fullmsg)
            if self.flog != None:
                self.flog.write(fullmsg + '\n')
                self.flog.flush()

    def error(self, msg): self.log(SimpleLogger.ERROR, msg)
    def warning(self, msg): self.log(SimpleLogger.WARNING, msg)
    def info(self, msg): self.log(SimpleLogger.INFO, msg)
    def debug(self, msg): self.log(SimpleLogger.DEBUG, msg)

class Logs:
    ROOT = "root"
    OBJECTS = "objects"
    MSAPI = "msapi"
    MSDB = "msdb"
    CREDODB = "credodb"

    def __init__(self):
        self.logs = {}
    def at(self, key):
        if key not in self.logs:
            self.logs[key] = SimpleLogger()
        return self.logs[key]
    def setRootFile(self, fpath = None):
        if fpath == None:
            fpath = sys.argv[0] + '.log'
        self.at(Logs.ROOT).setfile(fpath)

    def gerror(self, msg): self.at(Logs.ROOT).error(msg)
    def gwarning(self, msg): self.at(Logs.ROOT).warning(msg)
    def ginfo(self, msg): self.at(Logs.ROOT).info(msg)
    def gdebug(self, msg): self.at(Logs.ROOT).debug(msg)

    def fatal(self, key, msg):
        if key in self.logs:
            self.logs[key].fatal(msg)
        else:
            self.at(Logs.ROOT).fatal(msg)

    def error(self, key, msg):
        if key in self.logs:
            self.logs[key].error(msg)
        else:
            self.gerror(msg)

    def warning(self, key, msg):
        if key in self.logs:
            self.logs[key].warning(msg)
    def info(self, key, msg):
        if key in self.logs:
            self.logs[key].info(msg)
    def debug(self, key, msg):
        if key in self.logs:
            self.logs[key].debug(msg)
glogger = Logs()

if os.name == 'posix':  g_dirsep = '/'
if os.name == 'nt':     g_dirsep = '\\'
class Config:
    @staticmethod
    def load(path):
        parser = configparser.RawConfigParser()
        if os.path.exists(path): parser.read(path)
        return parser

class Object:
    def __init__(self):
        self.isTest = True
        self.isEnabled = False
        self.name = ''

        self.msid = 0
        self.targetid = ''
        self.targetregion = 'dev'

        self.caronline_login = ''
        self.caronline_password = ''
        self.caronline_macid = ''
        self.skey = ''

        self.events = {}
        
        self.can_milage = 0
        self.can_engine_rpm = 0
        self.can_engine_code = ''

class Objects:
    REGIONS = \
    [
     'dev',
     'kar',
    ]

    def __init__(self):
        self.map = {}
        
    def __setitem__(self, key, value):
        if not isinstance(value, Object):
            raise RuntimeError('Objects[%s]: value type "%s" is not Object' % (str(key), str(type(value))))
        self.map[key] = value
    def __len__(self):
        return len(self.map)
        
    def filter(self, regions=None, enabled=None, test=None):
        if isinstance(regions, int):
            regions = [regions]
        for o in self.map.values():
            if regions is not None and not o.targetregion in regions:
                continue
            if enabled is not None and o.isEnabled != enabled:
                continue
            if test is not None and o.isTest != test:
                continue
            yield o

    def sliceMac(self, mac, regions=None, enabled=None, test=None):
        found = []
        for o in self.filter(regions, enabled, test):
            if isinstance(mac, (str, unicode)):
                omac = str(o.caronline_macid)
                imac = str(mac)
                
                if omac.find(imac) != -1:
                    found.append(o)
            else:
                raise RuntimeError('sliceMac: wrong mac "%s" is specified - str only allowed' % (str(mac)))

        if len(found) == 0:
            raise RuntimeError('object(%d) is not found' % int(targetid))
        return found

    
    def sliceTargetId(self, targetid, regions=None, enabled=None, test=None):
        found = []
        for o in self.filter(regions, enabled, test):
            if isinstance(targetid, int):
                if int(o.targetid) == targetid:
                    found.append(o)
            elif isinstance(targetid, list):
                if int(o.targetid) > int(targetid[0]) \
                        and int(o.targetid) <= int(targetid[1]):
                    found.append(o)
            else:
                raise RuntimeError('sliceTargetId: wrong targetid "%s" is specified - int and list are only allowed' % (str(targetid)))

        if len(found) == 0:
            raise RuntimeError('object(%d) is not found' % int(targetid))
        return found

    def getTargetId(self, targetid, regions=None, enabled=None, test=None):
        found = self.sliceTargetId(targetid, regions, enabled, test)
        
        if len(found) != 1:
            msg = ''
            for o in found: msg += str(o.targetregion) + str(o.targetid) + ', '
            raise RuntimeError('object(%s) is ambigous: %s' % (str(targetid), str(msg)))
        return found[0]

    @staticmethod
    def filterNormalTest(objects, fname):
        try:
            f = open(fname, 'r')
        except:
            glogger.warning(Logs.OBJECTS, 'filterNormalTest: can\'t open file "%s" - all are test' % fname)
            for k, o in objects.items(): o.isTest = True
            return
    
        idsnorm = []
        for idx in f.readlines():
            try:
                idx = idx.strip()
                if len(idx): idsnorm.append(int(idx))
            except:
                continue
        glogger.debug(Logs.OBJECTS, 'filterNormalTest: loaded %d ids from "%s"' % (len(idsnorm), fname))
        
        norm = 0
        test = 0
        for o in objects.values():
            if int(o.targetid) in idsnorm:
                o.isTest = False
                norm += 1
            else:
                o.isTest = True
                test += 1
        glogger.info(Logs.OBJECTS, 'filterNormalTest: "%s"/%d -> normal(%d) test(%d)' % (fname, len(idsnorm), norm, test))
        
    def filterNormal(self, fname):
        Objects.filterNormalTest(self.map, fname)
        
    @staticmethod
    def makeTest(fname, targetid):
        try:
            f = open(fname, 'r')
        except:
            glogger.error(Logs.OBJECTS, 'makeTest: can\'t open file "%s"' % fname)
            
        idsnorm = []
        for idx in f.readlines():
            try:
                idx = idx.strip()
                if len(idx): idsnorm.append(int(idx))
            except:
                continue
        glogger.debug(Logs.OBJECTS, 'makeTest: loaded %d ids from "%s"' % (len(idsnorm), fname))
        
        if int(targetid) in idsnorm: idsnorm.remove(targetid)

        f = open(fname + '.save', 'w')
        for oid in sorted(idsnorm, key=int): f.write('%d\n' % int(oid))
        f.close()

        os.unlink(fname)
        os.rename(fname + '.save', fname)
        glogger.info(Logs.OBJECTS, 'makeTest: targetid(%s) is TEST -> "%s"/%d' % (str(targetid), fname, len(idsnorm)))
        return

    @staticmethod
    def makeNormal(fname, targetid):
        try:
            f = open(fname, 'r')
        except:
            glogger.error(Logs.OBJECTS, 'makeNormal: can\'t open file "%s"' % fname)
            
        idsnorm = []
        for idx in f.readlines():
            try:
                idx = idx.strip()
                if len(idx): idsnorm.append(int(idx))
            except:
                continue
        glogger.debug(Logs.OBJECTS, 'makeNormal: loaded %d ids from "%s"' % (len(idsnorm), fname))
        
        if int(targetid) not in idsnorm: idsnorm.append(targetid)

        f = open(fname + '.save', 'w')
        for oid in sorted(idsnorm, key=int): f.write('%d\n' % int(oid))
        f.close()

        os.unlink(fname)
        os.rename(fname + '.save', fname)
        glogger.info(Logs.OBJECTS, 'makeNormal: targetid(%s) is NORMAL -> "%s"/%d' % (str(targetid), fname, len(idsnorm)))
        return
        
class CarOnlineDB_Templates:
    def objectsLoad(self, dbconn, objects, regions=Objects.REGIONS):
        def parselogin(login):
            login = str(login)
            targetid = re.findall(r"\d+", login)
            if len(targetid) != 1:
                raise RuntimeError('parselogin: can\'t parse "%s"' % login)
            targetid = int(targetid[0])
            
            enabled = True
            buf = re.findall(r"del", login)
            if len(buf) == 0:
                enabled = True
            elif len(buf) == 1:
                enabled = False
            else:
                raise RuntimeError('parselogin: "%s" is totally disabled' % login)
                
            region = login.replace(str(targetid), '').replace('del', '').replace(' ', '')
            if len(region) == 0:
                region = 'dev'
            return (login, region, targetid, enabled) 
    
        dbcursor = dbconn.cursor()
        try:
            query = 'SELECT id,uid,lastEventTime,lastEventType,versionSoftPGSM FROM _devices'
            dbcursor.execute(query)
            uids = dbcursor.fetchall()
            enabled = 0
            for uid in uids:
                try:
                    query = 'SELECT login,password FROM _users WHERE defaultDevice=%s' % str(uid[0])
                    dbcursor.execute(query)
                    devdata = dbcursor.fetchall()
                    login   = str(devdata[0][0])
                    found = False
                    
                    obj = Object()
                    obj.caronline_login, obj.targetregion, obj.targetid, obj.isEnabled = parselogin(login)
                    if obj.targetregion not in regions:
                        glogger.debug(Logs.OBJECTS, 'fromMSDB: msid(%s) login(%s) ignored - region(%s) is not supported' % (str(uid[0]), str(obj.caronline_login), obj.targetregion))
                        continue
                    if obj.isEnabled:
                        enabled += 1

                    obj.msid = int(uid[0])
                    obj.caronline_macid = str(uid[1])
                    obj.caronline_password = str(devdata[0][1])
                    try:
                        obj.lastEventTime = uid[2]
                        obj.lastEventType = int(uid[3])
                        obj.versionSoftPGSM = str(uid[4])
                    except Exception as err:
                        glogger.debug(Logs.OBJECTS, 'fromMSDB: msid(%d) pcnid(%d) extra params: %s' % (obj.msid, obj.targetid, str(err)))
                        obj.lastEventTime = datetime.datetime(2012,1,1)
                        obj.lastEventType = 0
                        obj.versionSoftPGSM = 'Undefined'
                    objects[obj.msid] = obj
                    glogger.debug(Logs.OBJECTS,'fromMSDB: msid(%d) pcnid(%d) login(%s)' % (obj.msid, obj.targetid, obj.caronline_login))
                except Exception as err:
                    glogger.warning(Logs.OBJECTS, 'fromMSDB: msid(%s): %s' % (str(uid[0]), str(err)))
                    continue
            glogger.info(Logs.OBJECTS, 'fromMSDB: objects(%d/%d)' % (enabled, len(objects)))
            return True
        except Exception as err:
            glogger.error(Logs.OBJECTS, 'fromMSDB: %s' % str(err))

    def objectRename(self, dbconn, oldlogin, newlogin):
        cursor = dbconn.cursor()
        oldlogin = str(oldlogin)
        newlogin = str(newlogin)
        query = "UPDATE _users_internal SET login='%s' WHERE login='%s'" % (newlogin, oldlogin)
        cursor.execute(query)
        dbconn.commit()
        return True
        
    def objectGetPhotos(self, dbconn, msid, timestart):
        cursor = dbconn.cursor()
        query = "SELECT deviceId,eventTime,eventId,image FROM foto_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timestart.strftime('%Y-%m-%d %H:%M:%S')), str(msid))
        cursor.execute(query)
        photos = []
        data = cursor.fetchall()
        for buf in data:
            e = {}
            e['msid']       = buf[0]
            e['datetime']   = buf[1]
            e['eventid']    = buf[2]
            e['image']      = buf[3]
            photos.append(e)
        return photos

    def objectCreate(self, dbconn, region, oid, mac):
        cursor = dbconn.cursor()
        login   = str(region) + str(oid)
        pwd     = 'pass' + str(oid)
        query   = "SELECT login,password FROM _users_internal WHERE login='%s'" % (login)
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query1: %s' % (oid, query))
        cursor.execute(query)
        t = cursor.fetchall()
        if len(t) > 0 and t[0][0] == login: raise RuntimeError('device %s already exists!' % (login))

        query   = "INSERT INTO _devices_internal (uid, ServerCode) VALUES ('%s', '1')" % (str(mac))
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query2: %s' % (oid, query))
        cursor.execute(query)
        dbconn.commit()

        cursor  = dbconn.cursor()
        query   = "SELECT IDENT_CURRENT('_devices_internal')"
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query3: %s' % (oid, query))
        cursor.execute(query)
        t = cursor.fetchall()
        msid = int(t[0][0])

        query   = "INSERT INTO _users_internal (login, password, defaultDevice) VALUES ('%s', '%s', '%s')" % (login, pwd, str(msid))
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query4: %s' % (oid, query))
        cursor.execute(query)
        dbconn.commit()

        device_value = 'login=%s&password=%s&exportSupport=1' % (login, pwd)
        device_ctime = datetime.datetime.fromtimestamp(time.time()).strftime('%Y%m%d %H:%M:%S')
        query   = "INSERT INTO Security_values (\"key\", \"value\", \"date\") VALUES ('%s', '%s', '%s')" % (mac, device_value, device_ctime)
        glogger.debug(Logs.OBJECTS, 'registrateObjectInMSDB:%s: query5: %s' % (oid, query))
        cursor = dbconn.cursor()
        cursor.execute(query)
        dbconn.commit()

        deviceId = str(msid)
        gps = "127,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,40,26"
        gsm = "127,108,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,48,47,46,45,40,26"
        balance = "127,120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,40,39,35,34,33,32,31,30,29,28,27,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
        brk = "120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,44,43,40,35,34,33,32,31,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
        cam1 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam2 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam3 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam4 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam5 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam6 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam7 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        cam8 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
        gpsOff = "False"
        gsmOff = "False"
        brkOff = "False"
        balanceOff = "False"
        changeProgramMemory = "False"
        changeProgramGsm = "False"
        nameProgramFilePgsm = "CID_1240argus_TX9.bin"
        levelOff = "99" 
        levelBuff = "108,107,106,105,104,103,102,101,100,97,96,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,40,36,33,32,31,26"
        levelActivTCP ="94"
        levelActivGPRS = "91,90,89,88,87,86,48,47,46,45,39,35,34,29,27,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5"
        levelDeactivGPRS = "127,126,125,124,123,122,121,120,115,114,113,112,111,110,98,95,93,92,85,84,58,57,56,55,54,53,52,51,50,49,44,43,41,38,37,30,28,25,24,4,3,2,1"
        levelDeactivGPRSRoam = "42"
        changeVoice = "False"
        voiceFile = "voice.bin"

        queryPrefix = "INSERT INTO _device_transfer (\"deviceId\", \"gps\", \"gsm\", \"balance\", \"brk\", \"cam1\", \"cam2\", \"cam3\", \"cam4\", \"cam5\", \"cam6\", \"cam7\", \"cam8\", \"gpsOff\", \"gsmOff\", \"brkOff\", \"balanceOff\", \"changeProgramMemory\", \"changeProgramGsm\", \"nameProgramFilePgsm\", \"levelOff\", \"levelBuff\", \"levelActivTCP\", \"levelActivGPRS\", \"levelDeactivGPRS\", \"levelDeactivGPRSRoam\", \"changeVoice\", \"voiceFile\") VALUES "
        querySuffix = "('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (deviceId, gps, gsm, balance, brk, cam1, cam2, cam3, cam4, cam5, cam6, cam7, cam8, gpsOff, gsmOff, brkOff, balanceOff, changeProgramMemory, changeProgramGsm, nameProgramFilePgsm, levelOff, levelBuff, levelActivTCP, levelActivGPRS, levelDeactivGPRS, levelDeactivGPRSRoam, changeVoice, voiceFile)
        query = queryPrefix + querySuffix
        cursor = dbconn.cursor()
        cursor.execute(query)
        dbconn.commit()

        return (region, oid, mac, msid, login, pwd)

    def objectReloadFirmware(self, dbconn, msid, programFilePgsm):
        cursor = dbconn.cursor()
        check_query = "SELECT deviceId, nameProgramFilePgsm FROM _device_transfer WHERE deviceId='%s'" % (str(msid))
        cursor.execute(check_query)
        check_res = cursor.fetchall()
        if len(check_res) > 0 and len(check_res[0]) > 0:
            query = "UPDATE _device_transfer SET nameProgramFilePgsm='%s',changeProgramGsm='True' WHERE deviceId='%s'" % (programFilePgsm, str(msid))
            cursor.execute(query)
            dbconn.commit()
        else:
            gps = "127,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,40,26"
            gsm = "127,108,107,106,105,104,103,102,101,100,97,96,89,88,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,59,48,47,46,45,40,26"
            balance = "127,120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,40,39,35,34,33,32,31,30,29,28,27,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
            brk = "120,110,108,107,106,105,104,103,102,101,100,98,97,96,95,94,93,92,91,89,88,87,86,85,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,57,56,55,54,53,52,51,50,49,44,43,40,35,34,33,32,31,26,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1"
            cam1 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam2 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam3 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam4 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam5 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam6 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam7 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            cam8 = "127,127,127,127,127,127,127,127,126,126,126,126,126,126,126,126,125,125,125,125,125,125,125,125,124,124,124,124,124,124,124,124,123,123,123,123,123,123,123,123,122,122,122,122,122,122,122,122,121,121,121,121,121,121,121,121,120,120,120,120,120,120,120,120,115,115,115,115,115,115,115,115,114,114,114,114,114,114,114,114,113,113,113,113,113,113,113,113,112,112,112,112,112,112,112,112,111,111,111,111,111,111,111,111,110,110,110,110,110,110,110,110,108,108,108,108,108,108,108,108,107,107,107,107,107,107,107,107,106,106,106,106,106,106,106,106,105,105,105,105,105,105,105,105,104,104,104,104,104,104,104,104,103,103,103,103,103,103,103,103,102,102,102,102,102,102,102,102,101,101,101,101,101,101,101,101,100,100,100,100,100,100,100,100,99,99,99,99,99,99,99,99,98,98,98,98,98,98,98,98,97,97,97,97,97,97,97,97,96,96,96,96,96,96,96,96,95,95,95,95,95,95,95,95,94,94,94,94,94,94,94,94,93,93,93,93,93,93,93,93,92,92,92,92,92,92,92,92,91,91,91,91,91,91,91,91,90,90,90,90,90,90,90,90,88,88,88,88,88,88,88,88,"
            gpsOff = "False"
            gsmOff = "False"
            brkOff = "False"
            balanceOff = "False"
            changeProgramMemory = "False"
            changeProgramGsm = "True"
            nameProgramFilePgsm = str(programFilePgsm)
            levelOff = "99" 
            levelBuff = "108,107,106,105,104,103,102,101,100,97,96,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,40,36,33,32,31,26"
            levelActivTCP ="94"
            levelActivGPRS = "91,90,89,88,87,86,48,47,46,45,39,35,34,29,27,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5"
            levelDeactivGPRS = "127,126,125,124,123,122,121,120,115,114,113,112,111,110,98,95,93,92,85,84,58,57,56,55,54,53,52,51,50,49,44,43,41,38,37,30,28,25,24,4,3,2,1"
            levelDeactivGPRSRoam = "42"
            changeVoice = "False"
            voiceFile = "voice.bin"

            queryPrefix = "INSERT INTO _device_transfer (\"deviceId\", \"gps\", \"gsm\", \"balance\", \"brk\", \"cam1\", \"cam2\", \"cam3\", \"cam4\", \"cam5\", \"cam6\", \"cam7\", \"cam8\", \"gpsOff\", \"gsmOff\", \"brkOff\", \"balanceOff\", \"changeProgramMemory\", \"changeProgramGsm\", \"nameProgramFilePgsm\", \"levelOff\", \"levelBuff\", \"levelActivTCP\", \"levelActivGPRS\", \"levelDeactivGPRS\", \"levelDeactivGPRSRoam\", \"changeVoice\", \"voiceFile\") VALUES "
            querySuffix = "('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (str(msid), gps, gsm, balance, brk, cam1, cam2, cam3, cam4, cam5, cam6, cam7, cam8, gpsOff, gsmOff, brkOff, balanceOff, changeProgramMemory, changeProgramGsm, nameProgramFilePgsm, levelOff, levelBuff, levelActivTCP, levelActivGPRS, levelDeactivGPRS, levelDeactivGPRSRoam, changeVoice, voiceFile)
            query = queryPrefix + querySuffix
            cursor = dbconn.cursor()
            cursor.execute(query)
            dbconn.commit()
        return True

    def objectsLoadUnregistered(self, dbconn, path):
        objects = {}
        cursor = dbconn.cursor()
        dirname = path + g_dirsep + datetime.datetime.now().strftime('%Y.%m.%d') + g_dirsep + '-1' + g_dirsep + 'notregister_fail' + g_dirsep
        if os.path.isdir(dirname):
            for i in os.listdir(dirname):
                entry = {}
                entry['mac'] = i
                entry['path'] = dirname + i
                stat = os.stat(entry['path'])
                entry['modified'] = datetime.datetime.fromtimestamp(stat.st_mtime)
                entry['created'] = datetime.datetime.fromtimestamp(stat.st_ctime)
                query = "SELECT uid,id FROM _devices_internal WHERE uid='%s'" % (str(entry['mac']))
                cursor.execute(query)
                found = cursor.fetchall()
                if len(found) > 0 and len(found[0]) > 0:
                    entry['msid'] = str(found[0][1])
                objects[entry['mac']] = entry
        return objects

    def objectCountEventsByType(self, dbconn, msid, period):
        cursor = dbconn.cursor()
        query = "exec device_GetCountEventFromType %s" % (str(msid))
        if period != None:
            query += ", '%s', '%s'" % (period[0], period[1])
        cursor.execute(query)
        buff = cursor.fetchall()
        typecounts = {}
        for etype in buff:
            typecounts[int(etype[1])] = int(etype[2])
        return typecounts
        
    def getSupportedFirmwares(self, caronlinepath):
        versions = []
        dirname = caronlinepath + g_dirsep
        if os.path.isdir(dirname):
            for i in os.listdir(dirname):
                if len(i) <= 4 or i[0:4] != 'CID_': continue
                versions.append( (i, i) )
        return versions
        
class CarOnlineDB:
    def __init__(self, dbparams):
        '''
        params are:
        {
         'host':...,
         'port':...,
         'dbname':...,
         'user':...,
         'password':...,
        }
        '''
        self.dbparams = dbparams
        self.dbapi = CarOnlineDB_Templates()

    def getconnection(self):
        if MSDBDriver == "pymssql":
            return pymssql.connect(host=self.dbparams['host'] + ':' + self.dbparams['port'],
                                   database=self.dbparams['dbname'], user=self.dbparams['user'], password=self.dbparams['password'])
        if MSDBDriver == "pyodbc":
            return pyodbc.connect(driver='{SQL Server}', server=self.dbparams['host'], port=self.dbparams['port'],
                                  database=self.dbparams['dbname'], uid=self.dbparams['user'], pwd=self.dbparams['password'])
        glogger.error(Logs.MSDB, 'getconnection: no any db driver loaded!')
        raise RuntimeError('no any db driver loaded')

    def objectsLoad(self, objs, prefixes=Objects.REGIONS):
        return self.dbapi.objectsLoad(self.getconnection(), objs, prefixes)

    def objectsLoadUnregistered(self, path):
        return self.dbapi.objectsLoadUnregistered(self.getconnection(), path)

    def objectRename(self, oldlogin, newlogin):
        return self.dbapi.objectRename(self.getconnection(), oldlogin, newlogin)

    def objectCreate(self, region, pcnid, mac):
        return self.dbapi.objectCreate(self.getconnection(), region, pcnid, mac)

    def objectReloadFirmware(self, msid, filename):
        return self.dbapi.objectReloadFirmware(self.getconnection(), msid, filename)

    def objectCountEventsByType(self, msid, period):
        return self.dbapi.objectCountEventsByType(self.getconnection(), msid, period)
        
    def objectGetPhotos(self, msid, timestart):
        return self.dbapi.objectGetPhotos(self.getconnection(), msid, timestart)